with network as (
    select case
               when network_type = 3 then 1 --加盟
               when network_type = 4 then 2 --中心
               when network_type = 5 then 3 --集散
               when network_type = 6 then 4 --网点
               when network_type = 2 then 5 --代理区
               when network_type = 1 then 6 --总部
               else 0
        end            as network_type
         , code
         , name        as network_name     --网点名称
         , agent_code                      --代理区编码
         , agent_name                      --代理区名称
         , fran_code   as franchisee_code  --加盟商编码
         , fran_name   as franchisee_name  --加盟商名称
         , center_code as transcenter_code --转运中心编码
         , center_name as transcenter_name --转运中心名称
    from jms_dim.dim_network_whole_massage
)
insert
overwrite
table
jms_dwd.dwd_tab_barscan_deliver_base_hi
partition
(
dt
)
select barscan.recordid              as record_id
     , barscan.billcode              as waybill_no
     , barscan.listcode              as list_code
     , barscan.subbillcode           as subbill_code
     , barscan.transfercode          as transfer_code
     , barscan.packagecode           as package_code
     , barscan.scantype              as scan_type
     , barscan.nextstation           as next_station
     , barscan.destination           as destination
     , barscan.scanuser              as scan_user
     , barscan.inputsite             as scan_site
     , barscan.scantime              as scan_time
     , barscan.inputtime             as input_time
     , barscan.operatedate           as operate_date
     , barscan.send_deliver_user     as send_user
     , barscan.sendcustomer          as agree_customer
     , barscan.sendsite              as send_site
     , barscan.destsite              as dest_site
     , barscan.transfercenter        as transfer_center
     , barscan.pcs                   as pcs
     , barscan.weight                as weight
     , barscan.goodstype             as goods_type
     , barscan.expresstype           as express_type
     , barscan.shifts                as shifts_code
     , barscan.transfer_deliver_fee  as transfer_deliver_fee
     , barscan.networkfee            as network_manage_fee
     , barscan.otherfee              as other_fee
     , barscan.checkflag             as check_flag
     , barscan.accountflag           as account_flag
     , barscan.receiptflag           as receipt_flag
     , barscan.returnflag            as return_flag
     , barscan.elescaleflag          as ele_scale_flag
     , barscan.unrecordflag          as unrecord_flag
     , barscan.writebackflag         as is_protocol
     , barscan.lockflag              as lock_flag
     , barscan.remark                as width
     , barscan.modifyuser            as modify_user
     , barscan.modifytime            as modify_time
     , barscan.mobile                as length
     , barscan.pistolid              as pistol_id
     , barscan.remark1               as height
     , barscan.remark2               as box_num
     , barscan.scansitecode          as scan_site_code
     , barscan.uploadtime            as upload_time
     , barscan.remark3               as len_wid_hei_product
     , barscan.scanusercode          as scan_user_code
     , barscan.send_deliver_usercode as send_user_code
     , barscan.nextstationcode       as next_station_code
     , barscan.source                as source
     , barscan.remark5               as remark5
     , barscan.remark6               as remark6
     , barscan.remark4               as list_code2
     , barscan.scansiteid            as scan_site_id
     , barscan.nextstationid         as next_station_id
     , barscan.scanuserid            as scan_user_id
     , barscan.send_deliver_userid   as send_deliver_user_id
     , network.network_type          as network_type
     , network.agent_code            as site_agent_code
     , network.agent_name            as site_agent_name
     , network.franchisee_code       as site_franchisee_code
     , network.franchisee_name       as site_franchisee_name
     , network.transcenter_code      as site_transcenter_code
     , network.transcenter_name      as site_transcenter_name
     , network_pre.network_type      as next_network_type
     , case
           when (length(billcode) = 13) then 'GB'
           when (length(billcode) = 15 and billcode like "55%") then 'BS'
           when (length(billcode) = 12 and (billcode like "40%" or billcode like "42%")) then 'BSB'
           when (length(billcode) = 15 and billcode like "JT%") then 'JT'
           when (length(billcode) = 13 and billcode like "B%") then 'JTB'
           else "Others"
    end                              as waybill_source
     , barscan.dt                    as dt
from jms_ods.tab_barscan_deliver_hi barscan
         left join network network on barscan.scansitecode = network.code
         left join network network_pre on barscan.nextstationcode = network_pre.code
where barscan.dt = '{{ execution_date | cst_hour }}';
